
DROP TABLE S_EMPLOYEES;
CREATE TABLE S_EMPLOYEES (
  ID VARCHAR2(20) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(40),
  SEX VARCHAR2(30),
  BIRTH VARCHAR2(30),
  PHONE VARCHAR2(15),
  EMAIL VARCHAR2(100),
  JOBTITLE_SEQ NUMBER(8, 0),
  STATUS_SEQ NUMBER(8, 0),
  JOBCATEGORY_SEQ NUMBER(8, 0),
  DEPARTMENT_SEQ NUMBER(8, 0),
  SUPERVISOR_ID NUMBER(8, 0),
  PHOTONAME VARCHAR2(100)
);
DROP TABLE S_ROLE;
CREATE TABLE S_ROLE (
	ID NUMBER(8, 0) NOT NULL PRIMARY KEY,
	ROLE VARCHAR2(10)
);
DROP SEQUENCE role_seq;
CREATE SEQUENCE role_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_role_seq
BEFORE INSERT    --
ON S_ROLE
FOR EACH ROW
BEGIN
  SELECT role_seq.Nextval
  INTO:NEW.ID  FROM DUAL;
END;
/

DROP TABLE S_USERS;
CREATE TABLE S_USERS (
	USERNAME VARCHAR2(40) NOT NULL PRIMARY KEY,
  PASSWORD VARCHAR2(50),
  ROLEID NUMBER(8, 0),
  EMPLOYEEID VARCHAR2(20)
);

DROP TABLE S_DEPARTMENT;
CREATE TABLE S_DEPARTMENT (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50),
  DESCRIPTION VARCHAR2(300),
  PARENT_SEQ NUMBER(8, 0)
);
DROP SEQUENCE department_seq;
CREATE SEQUENCE department_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_department_seq
BEFORE INSERT    --
ON S_DEPARTMENT
FOR EACH ROW
BEGIN
  SELECT department_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_STATUS;
CREATE TABLE S_STATUS (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE status_seq;
CREATE SEQUENCE status_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_status_seq
BEFORE INSERT    --
ON S_STATUS
FOR EACH ROW
BEGIN
  SELECT status_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_EVAL_GROUP;
CREATE TABLE S_EVAL_GROUP (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50),
  CHOOSE NUMBER(2)
);
DROP SEQUENCE eg_seq;
CREATE SEQUENCE eg_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_eg_seq
BEFORE INSERT    --
ON S_EVAL_GROUP
FOR EACH ROW
BEGIN
  SELECT eg_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_EVAL_GROUP(NAME, CHOOSE) VALUES('Main Evaluator', 1);
INSERT INTO S_EVAL_GROUP(NAME, CHOOSE) VALUES('Self', 1);
INSERT INTO S_EVAL_GROUP(NAME, CHOOSE) VALUES('Supervisors', 1);
INSERT INTO S_EVAL_GROUP(NAME, CHOOSE) VALUES('Peers', 1);
INSERT INTO S_EVAL_GROUP(NAME, CHOOSE) VALUES('Subordinates', 1);


DROP TABLE S_QUESTION;
CREATE TABLE S_QUESTION (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(500)
);
DROP SEQUENCE question_seq;
CREATE SEQUENCE question_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_question_seq
BEFORE INSERT    --
ON S_QUESTION
FOR EACH ROW
BEGIN
  SELECT question_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_QUESTION(NAME) VALUES('What goals did you accomplish since your last evaluation (or hire)?');
INSERT INTO S_QUESTION(NAME) VALUES('What goals were you unable to accomplish and what hindered you from achieving them?');
INSERT INTO S_QUESTION(NAME) VALUES('What goals will you set for the next period?');
INSERT INTO S_QUESTION(NAME) VALUES('What resources do you need from the organization to achieve these goals?');
INSERT INTO S_QUESTION(NAME) VALUES('Based on YOUR personal satisfaction with your job (workload, environment, pay, challenge, etc.) how would you rate your satisfaction from 1 (poor) to 10 (excellent.)');

DROP TABLE N_QUESTION;
CREATE TABLE N_QUESTION (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(500),
  TYPE_SEQ NUMBER(8, 0),
  ADD_DATE VARCHAR2(30),
  MOD_DATE VARCHAR2(30)
);
DROP SEQUENCE nq_seq;
CREATE SEQUENCE nq_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_nq_seq
BEFORE INSERT    --
ON N_QUESTION
FOR EACH ROW
BEGIN
  SELECT nq_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('Your Gender:', 1, '2014-10-10', '2014-10-10');
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('Your Age:', 1, '2014-10-10', '2014-10-10');
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('Your Education Background:', 1, '2014-10-10', '2014-10-10');
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('How much money do you spend on reading newspapers?', 1, '2014-10-10', '2014-10-10');
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('Which kind of newspaper do you like most?', 2, '2014-10-10', '2014-10-10');
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('Where do you often go when you are free?', 2, '2014-10-10', '2014-10-10');
INSERT INTO N_QUESTION(NAME, TYPE_SEQ, ADD_DATE, MOD_DATE) VALUES('Do you have any suggestions for us?', 3, '2014-10-10', '2014-10-10');


DROP TABLE N_QUESTION_TYPE;
CREATE TABLE N_QUESTION_TYPE (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(500)
);
DROP SEQUENCE nqt_seq;
CREATE SEQUENCE nqt_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_nqt_seq
BEFORE INSERT    --
ON N_QUESTION_TYPE
FOR EACH ROW
BEGIN
  SELECT nqt_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO N_QUESTION_TYPE(NAME) VALUES('Single Choice');
INSERT INTO N_QUESTION_TYPE(NAME) VALUES('Multiple Choice');
INSERT INTO N_QUESTION_TYPE(NAME) VALUES('Short Answer');

DROP TABLE N_ITEM;
CREATE TABLE N_ITEM (
  QUESTION_SEQ NUMBER(8, 0),
  ITEM VARCHAR2(200)
);
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(1, 'Male');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(1, 'Female');

INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(2, '0 - 20');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(2, '20 - 25');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(2, '25 - 30');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(2, '30 - 100');

INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'PHD');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'Master');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'Bachelor');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'High School');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'Middle School');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'Primary School');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(3, 'Keep secret');

INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(4, '0 - 100$');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(4, '100 - 500$');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(4, 'More than 500$');

INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Global News');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Local News');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Entertainment');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Science');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Games');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Sports');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(5, 'Beautiful Articles');

INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Mall');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Cinema');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Theater');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Home');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Travel');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Book Store');
INSERT INTO N_ITEM(QUESTION_SEQ, ITEM) VALUES(6, 'Village');


DROP TABLE N_QUESTIONNAIRE;
CREATE TABLE N_QUESTIONNAIRE (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(500),
  DESCRIPTION VARCHAR2(1000),
  PROMOTION_SEQ NUMBER(8, 0),
  ADD_DATE VARCHAR2(30),
  MOD_DATE VARCHAR2(30),
  PUBLISHED NUMBER(1)
);
DROP SEQUENCE questionnaire_seq;
CREATE SEQUENCE questionnaire_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_questionnaire_seq
BEFORE INSERT    --
ON N_QUESTIONNAIRE
FOR EACH ROW
BEGIN
  SELECT questionnaire_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO N_QUESTIONNAIRE(NAME, DESCRIPTION, PROMOTION_SEQ, ADD_DATE, MOD_DATE, PUBLISHED) VALUES('"Guangming Daily" market survey', 'Hello! We are students from NP Pillar. We make the newspaper market survey. Please choose the answer for the following questions according to your own situation. Your answers will be much helpful for us.', 3, '2014-10-10', '2014-10-10', 1);
INSERT INTO N_QUESTIONNAIRE(NAME, DESCRIPTION, PROMOTION_SEQ, ADD_DATE, MOD_DATE, PUBLISHED) VALUES('Market research on the newspapers for elderly.', 'Hello! We are students from NP Pillar. We make the newspaper market survey. Please choose the answer for the following questions according to your own situation. Your answers will be much helpful for us.', 4, '2014-10-10', '2014-10-10', 1);
INSERT INTO N_QUESTIONNAIRE(NAME, DESCRIPTION, PROMOTION_SEQ, ADD_DATE, MOD_DATE, PUBLISHED) VALUES('Market survey for newspaper "Times"', 'Hello! We are students from NP Pillar. We make the newspaper market survey. Please choose the answer for the following questions according to your own situation. Your answers will be much helpful for us.', 3, '2014-10-10', '2014-10-10', 1);


DROP TABLE N_QUESTIONNAIRE_QUESTION;
CREATE TABLE N_QUESTIONNAIRE_QUESTION (
  QUESTIONNAIRE_SEQ NUMBER(8, 0),
  QUESTION_SEQ NUMBER(8, 0),
  INDEX_NUMBER NUMBER(8, 0)
);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 1, 0);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 2, 1);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 3, 2);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 4, 3);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 5, 4);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 6, 5);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(1, 7, 6);

INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 1, 0);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 2, 1);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 3, 2);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 4, 3);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 5, 4);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 6, 5);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(2, 7, 6);

INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 1, 0);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 2, 1);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 3, 2);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 4, 3);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 5, 4);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 6, 5);
INSERT INTO N_QUESTIONNAIRE_QUESTION(QUESTIONNAIRE_SEQ, QUESTION_SEQ, INDEX_NUMBER) VALUES(3, 7, 6);


DROP TABLE N_QUESTIONNAIRE_RECORD;
CREATE TABLE N_QUESTIONNAIRE_RECORD (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  QUESTIONNAIRE_SEQ NUMBER(8),
  ADD_DATE VARCHAR2(40),
  ECLAPSED_TIME NUMBER(8),
  IP VARCHAR2(40)
);
DROP SEQUENCE qr_seq;
CREATE SEQUENCE qr_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_qr_seq
BEFORE INSERT    --
ON N_QUESTIONNAIRE_RECORD
FOR EACH ROW
BEGIN
  SELECT qr_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO N_QUESTIONNAIRE_RECORD(QUESTIONNAIRE_SEQ, ADD_DATE, ECLAPSED_TIME, IP) VALUES(1, '2014-10-10 10:10:10', 50, '172.26.142.74');
INSERT INTO N_QUESTIONNAIRE_RECORD(QUESTIONNAIRE_SEQ, ADD_DATE, ECLAPSED_TIME, IP) VALUES(1, '2014-10-11 11:10:10', 60, '172.26.142.74');
INSERT INTO N_QUESTIONNAIRE_RECORD(QUESTIONNAIRE_SEQ, ADD_DATE, ECLAPSED_TIME, IP) VALUES(1, '2014-10-12 12:10:10', 70, '172.26.142.74');
INSERT INTO N_QUESTIONNAIRE_RECORD(QUESTIONNAIRE_SEQ, ADD_DATE, ECLAPSED_TIME, IP) VALUES(1, '2014-10-13 13:10:10', 80, '172.26.142.74');


DROP TABLE N_QUESTION_LOG;
CREATE TABLE N_QUESTION_LOG
(
  RECORD_SEQ NUMBER(8, 0),
  QUESTION_SEQ NUMBER(8, 0),
  ITEM VARCHAR2(200),
  ANSWER VARCHAR2(500)
);
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 1, 'Male', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 1, 'Female', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 1, 'Female', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 1, 'Female', '');

INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 2, '0 - 20', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 2, '20 - 25', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 2, '25 - 30', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 2, '30 - 100', '');

INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 3, 'PHD', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 3, 'Master', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 3, 'High School', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 3, 'High School', '');

INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 4, '0 - 100$', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 4, '100 - 500$', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 4, '100 - 500$', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 4, 'More than 500$', '');

INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 5, 'Global News', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 5, 'Local News', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 5, 'Entertainment', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 5, 'Science', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 5, 'Games', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 5, 'Science', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 5, 'Sports', '');

INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 6, 'Mall', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 6, 'Home', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 6, 'Book Store', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 6, 'Village', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 6, 'Book Store', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 6, 'Mall', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 6, 'Village', '');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 6, 'Cinema', '');

INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(1, 7, '', 'Answer for this question.');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(2, 7, '', 'Answer for this question.');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(3, 7, '', 'Answer for this question.');
INSERT INTO N_QUESTION_LOG(RECORD_SEQ, QUESTION_SEQ, ITEM, ANSWER) VALUES(4, 7, '', 'Answer for this question.');

DROP TABLE S_TEMPLATE;
CREATE TABLE S_TEMPLATE (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(500)
);
DROP SEQUENCE template_seq;
CREATE SEQUENCE template_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_template_seq
BEFORE INSERT    --
ON S_TEMPLATE
FOR EACH ROW
BEGIN
  SELECT template_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_TEMPLATE(NAME) VALUES('Mid Year Review');
INSERT INTO S_TEMPLATE(NAME) VALUES('Performance Management Review');
INSERT INTO S_TEMPLATE(NAME) VALUES('Annual Employee Performance Review');
INSERT INTO S_TEMPLATE(NAME) VALUES('Performance and Development Plan');
INSERT INTO S_TEMPLATE(NAME) VALUES('End of Year Individual Performance');
INSERT INTO S_TEMPLATE(NAME) VALUES('Annual Performance Review 2014-2015');

DROP TABLE S_APPRAISAL;
CREATE TABLE S_APPRAISAL (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  EMPLOYEE_ID VARCHAR2(40),
  DATE_FROM VARCHAR2(50),
  DATE_TO VARCHAR2(50),
  DUE VARCHAR2(50),
  DESCRIPTION VARCHAR2(200),
  TEMPLATE_SEQ NUMBER(8),
  CWEIGHT NUMBER(5, 2),
  GWEIGHT NUMBER(5, 2),
  QWEIGHT NUMBER(5, 2)
);
DROP SEQUENCE appraisal_seq;
CREATE SEQUENCE appraisal_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_appraisal_seq
BEFORE INSERT    --
ON S_APPRAISAL
FOR EACH ROW
BEGIN
  SELECT appraisal_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_APPRAISAL(EMPLOYEE_ID, DATE_FROM, DATE_TO, DUE, DESCRIPTION, TEMPLATE_SEQ, CWEIGHT, GWEIGHT, QWEIGHT) VALUES(10001, '2014-09-01', '2014-11-01', '2014-11-03', 'Q3 Review', 2, 50, 50, 0);
INSERT INTO S_APPRAISAL(EMPLOYEE_ID, DATE_FROM, DATE_TO, DUE, DESCRIPTION, TEMPLATE_SEQ, CWEIGHT, GWEIGHT, QWEIGHT) VALUES(10002, '2014-03-01', '2014-07-01', '2014-07-03', 'Q2 Review', 1, 30, 50, 20);
INSERT INTO S_APPRAISAL(EMPLOYEE_ID, DATE_FROM, DATE_TO, DUE, DESCRIPTION, TEMPLATE_SEQ, CWEIGHT, GWEIGHT, QWEIGHT) VALUES(10003, '2014-01-01', '2014-04-01', '2014-04-03', 'Q1 Review', 3, 30, 30, 40);


DROP TABLE S_APPRAISAL_REVIEWER;
CREATE TABLE S_APPRAISAL_REVIEWER (
  APPRAISAL_SEQ NUMBER(8),
  EMPLOYEE_ID VARCHAR2(40),
  EG_SEQ NUMBER(8)
);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (1, 10002, 1);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (1, 10003, 1);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (1, 10004, 1);

INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (2, 10005, 1);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (2, 10006, 1);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (2, 10007, 1);

INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (3, 10001, 1);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (3, 10002, 1);
INSERT INTO S_APPRAISAL_REVIEWER(APPRAISAL_SEQ, EMPLOYEE_ID, EG_SEQ) VALUES (3, 10007, 1);


DROP TABLE S_AC_LOG;
CREATE TABLE S_AC_LOG (
  APPRAISAL_SEQ NUMBER(8),
  EMPLOYEE_ID VARCHAR2(40),
  COMPETENCY_SEQ NUMBER(8),
  RATE NUMBER(3),
  COMMENTS VARCHAR2(500)
);
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10002, 1, 10, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10002, 2, 8, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10002, 3, 7, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10002, 4, 6, 'Comment about this competency');

INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10003, 1, 8, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10003, 2, 7, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10003, 3, 6, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10003, 4, 5, 'Comment about this competency');

INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10004, 1, 7, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10004, 2, 6, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10004, 3, 5, 'Comment about this competency');
INSERT INTO S_AC_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, COMPETENCY_SEQ, RATE, COMMENTS) VALUES(1, 10004, 4, 4, 'Comment about this competency');



DROP TABLE S_AG_LOG;
CREATE TABLE S_AG_LOG (
  APPRAISAL_SEQ NUMBER(8),
  EMPLOYEE_ID VARCHAR2(40),
  GOAL_SEQ NUMBER(8),
  RATE NUMBER(3),
  COMMENTS VARCHAR2(500)
);

INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10002, 1, 1, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10002, 2, 2, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10002, 3, 3, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10002, 4, 4, 'Comment about this goal');

INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10003, 1, 5, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10003, 2, 6, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10003, 3, 7, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10003, 4, 8, 'Comment about this goal');

INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10004, 1, 9, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10004, 2, 10, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10004, 3, 1, 'Comment about this goal');
INSERT INTO S_AG_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, GOAL_SEQ, RATE, COMMENTS) VALUES(1, 10004, 4, 2, 'Comment about this goal');



DROP TABLE S_AQ_LOG;
CREATE TABLE S_AQ_LOG (
  APPRAISAL_SEQ NUMBER(8),
  EMPLOYEE_ID VARCHAR2(40),
  QUESTION_SEQ NUMBER(8),
  RATE NUMBER(3),
  COMMENTS VARCHAR2(2000)
);

INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10002, 1, 3, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10002, 2, 4, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10002, 3, 5, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10002, 4, 6, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10002, 5, 7, 'Comment about this question');


INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10003, 1, 7, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10003, 2, 8, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10003, 3, 9, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10003, 4, 10, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10003, 5, 1, 'Comment about this question');


INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10004, 1, 1, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10004, 2, 2, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10004, 3, 3, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10004, 4, 4, 'Comment about this question');
INSERT INTO S_AQ_LOG(APPRAISAL_SEQ, EMPLOYEE_ID, QUESTION_SEQ, RATE, COMMENTS) VALUES(1, 10004, 5, 5, 'Comment about this question');


DROP TABLE S_APPRAISAL_COMPETENCY;
CREATE TABLE S_APPRAISAL_COMPETENCY (
  APPRAISAL_SEQ NUMBER(8),
  COMPETENCY_SEQ NUMBER(8),
  RATIO NUMBER(3)
);
INSERT INTO S_APPRAISAL_COMPETENCY(APPRAISAL_SEQ, COMPETENCY_SEQ, RATIO) VALUES(1, 1, 10);
INSERT INTO S_APPRAISAL_COMPETENCY(APPRAISAL_SEQ, COMPETENCY_SEQ, RATIO) VALUES(1, 2, 20);
INSERT INTO S_APPRAISAL_COMPETENCY(APPRAISAL_SEQ, COMPETENCY_SEQ, RATIO) VALUES(1, 3, 30);
INSERT INTO S_APPRAISAL_COMPETENCY(APPRAISAL_SEQ, COMPETENCY_SEQ, RATIO) VALUES(1, 4, 40);


DROP TABLE S_APPRAISAL_GOAL;
CREATE TABLE S_APPRAISAL_GOAL (
  APPRAISAL_SEQ NUMBER(8),
  GOAL_SEQ NUMBER(8),
  RATIO NUMBER(3)
);
INSERT INTO S_APPRAISAL_GOAL(APPRAISAL_SEQ, GOAL_SEQ, RATIO) VALUES (1, 1, 40);
INSERT INTO S_APPRAISAL_GOAL(APPRAISAL_SEQ, GOAL_SEQ, RATIO) VALUES (1, 2, 30);
INSERT INTO S_APPRAISAL_GOAL(APPRAISAL_SEQ, GOAL_SEQ, RATIO) VALUES (1, 3, 20);
INSERT INTO S_APPRAISAL_GOAL(APPRAISAL_SEQ, GOAL_SEQ, RATIO) VALUES (1, 4, 10);


DROP TABLE S_APPRAISAL_QUESTION;
CREATE TABLE S_APPRAISAL_QUESTION (
  APPRAISAL_SEQ NUMBER(8),
  QUESTION_SEQ NUMBER(8),
  RATIO NUMBER(3)
);
INSERT INTO S_APPRAISAL_QUESTION(APPRAISAL_SEQ, QUESTION_SEQ, RATIO) VALUES(1, 1, 20);
INSERT INTO S_APPRAISAL_QUESTION(APPRAISAL_SEQ, QUESTION_SEQ, RATIO) VALUES(1, 2, 20);
INSERT INTO S_APPRAISAL_QUESTION(APPRAISAL_SEQ, QUESTION_SEQ, RATIO) VALUES(1, 3, 10);
INSERT INTO S_APPRAISAL_QUESTION(APPRAISAL_SEQ, QUESTION_SEQ, RATIO) VALUES(1, 4, 30);
INSERT INTO S_APPRAISAL_QUESTION(APPRAISAL_SEQ, QUESTION_SEQ, RATIO) VALUES(1, 5, 20);


DROP TABLE S_GOAL_STATUS;
CREATE TABLE S_GOAL_STATUS (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE gstatus_seq;
CREATE SEQUENCE gstatus_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_gstatus_seq
BEFORE INSERT    --
ON S_GOAL_STATUS
FOR EACH ROW
BEGIN
  SELECT gstatus_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_GOAL_TYPE;
CREATE TABLE S_GOAL_TYPE (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE gtype_seq;
CREATE SEQUENCE gtype_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_gtype_seq
BEFORE INSERT    --
ON S_GOAL_TYPE
FOR EACH ROW
BEGIN
  SELECT gtype_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_REMINDER;
CREATE TABLE S_REMINDER (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE reminder_seq;
CREATE SEQUENCE reminder_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_reminder_seq
BEFORE INSERT    --
ON S_REMINDER
FOR EACH ROW
BEGIN
  SELECT reminder_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_REMINDER(NAME) VALUES('None');
INSERT INTO S_REMINDER(NAME) VALUES('5 Minutes Before');
INSERT INTO S_REMINDER(NAME) VALUES('10 Minutes Before');
INSERT INTO S_REMINDER(NAME) VALUES('Half an hour Before');
INSERT INTO S_REMINDER(NAME) VALUES('1 hour Before');
INSERT INTO S_REMINDER(NAME) VALUES('2 hours Before');
INSERT INTO S_REMINDER(NAME) VALUES('Half a day Before');
INSERT INTO S_REMINDER(NAME) VALUES('1 day Before');


DROP TABLE S_JOBCATEGORY;
CREATE TABLE S_JOBCATEGORY (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE category_seq;
CREATE SEQUENCE category_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_category_seq
BEFORE INSERT    --
ON S_JOBCATEGORY
FOR EACH ROW
BEGIN
  SELECT category_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_JOBTITLE;
CREATE TABLE S_JOBTITLE (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(100),
  DESCRIPTION VARCHAR2(500),
  NOTE VARCHAR2(500)
);
DROP SEQUENCE jobtitle_seq;
CREATE SEQUENCE jobtitle_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_jobtitle_seq
BEFORE INSERT    --
ON S_JOBTITLE
FOR EACH ROW
BEGIN
  SELECT jobtitle_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_COMPETENCY;
CREATE TABLE S_COMPETENCY (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(100),
  DESCRIPTION VARCHAR2(500)
);
DROP SEQUENCE competency_seq;
CREATE SEQUENCE competency_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_competency_seq
BEFORE INSERT    --
ON S_COMPETENCY
FOR EACH ROW
BEGIN
  SELECT competency_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_POSITION;
CREATE TABLE S_POSITION (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50),
  DESCRIPTION VARCHAR2(200)
);
DROP SEQUENCE position_seq;
CREATE SEQUENCE position_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_position_seq
BEFORE INSERT    --
ON S_POSITION
FOR EACH ROW
BEGIN
  SELECT position_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_PUNCH;
CREATE TABLE S_PUNCH (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  EMPLOYEEID VARCHAR2(40),
  INTIME VARCHAR2(30),
  INNOTE VARCHAR2(200),
  OUTTIME VARCHAR2(30),
  OUTNOTE VARCHAR2(200)
);
DROP SEQUENCE punch_seq;
CREATE SEQUENCE punch_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_punch_seq
BEFORE INSERT    --
ON S_PUNCH
FOR EACH ROW
BEGIN
  SELECT punch_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE S_SEX;
CREATE TABLE S_SEX (
  NAME VARCHAR2(30) NOT NULL PRIMARY KEY
);

DROP TABLE S_SCHEDULE;
CREATE TABLE S_SCHEDULE
(
	ID VARCHAR2(100) NOT NULL PRIMARY KEY,
	EMPLOYEE_ID VARCHAR2(20),
	TITLE VARCHAR2(50),
	DATE_FROM VARCHAR2(30),
	DATE_TO VARCHAR2(30),
	REMINDER_SEQ NUMBER(8, 0),
	NOTE VARCHAR2(200),
	COMPLETE NUMBER(3),
	NOTIFICATION NUMBER(3),
	EMAIL VARCHAR2(100)
);
INSERT INTO S_SCHEDULE(ID, EMPLOYEE_ID, TITLE, DATE_FROM, DATE_TO, REMINDER_SEQ, NOTE, COMPLETE, NOTIFICATION, EMAIL) VALUES (1, 10001, 'Meeting with client', '2014-11-09 10:00:00', '2014-11-09 12:00:00', 2, 'At meeting room on 22F', 1, 1, 'allen@worksap.co.jp');
INSERT INTO S_SCHEDULE(ID, EMPLOYEE_ID, TITLE, DATE_FROM, DATE_TO, REMINDER_SEQ, NOTE, COMPLETE, NOTIFICATION, EMAIL) VALUES (2, 10001, 'Make Issue 2 for Fortune', '2014-11-11 09:00:00', '2014-11-11 12:00:00', 3, 'At office', 0, 1, 'allen@worksap.co.jp');
INSERT INTO S_SCHEDULE(ID, EMPLOYEE_ID, TITLE, DATE_FROM, DATE_TO, REMINDER_SEQ, NOTE, COMPLETE, NOTIFICATION, EMAIL) VALUES (3, 10001, 'Promotion activity at Jiaotong University', '2014-11-12 11:00:00', '2014-11-12 12:00:00', 2, 'At Jiaotong university', 0, 1, 'allen@worksap.co.jp');


DROP TABLE S_TRACKER;
CREATE TABLE S_TRACKER (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(200),
  EMPLOYEE_ID VARCHAR2(40),
  ADD_DATE VARCHAR2(30),
  MOD_DATE VARCHAR2(30)
);
DROP SEQUENCE tracker_seq;
CREATE SEQUENCE tracker_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_tracker_seq
BEFORE INSERT    --
ON S_TRACKER
FOR EACH ROW
BEGIN
  SELECT tracker_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

INSERT INTO S_TRACKER(NAME, EMPLOYEE_ID, ADD_DATE, MOD_DATE) VALUES('Tracker for Allen', '10001', '2014-09-10 11:11:11', '2014-10-10 11:11:11');
INSERT INTO S_TRACKER(NAME, EMPLOYEE_ID, ADD_DATE, MOD_DATE) VALUES('Tracker for Byron', '10002', '2014-08-10 11:11:11', '2014-10-10 11:11:11');
INSERT INTO S_TRACKER(NAME, EMPLOYEE_ID, ADD_DATE, MOD_DATE) VALUES('Tracker for Lei', '10004', '2014-07-10 11:11:11', '2014-10-10 11:11:11');


DROP TABLE S_TRACKER_REVIEWER;
CREATE TABLE S_TRACKER_REVIEWER (
  TRACKER_SEQ NUMBER(8, 0),
  EMPLOYEE_ID VARCHAR2(40)
);
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(1, '10002');
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(1, '10003');
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(1, '10004');

INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(2, '10004');
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(2, '10005');
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(2, '10006');

INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(3, '10007');
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(3, '10001');
INSERT INTO S_TRACKER_REVIEWER(TRACKER_SEQ, EMPLOYEE_ID) VALUES(3, '10008');


DROP TABLE S_TRACKER_LOG;
CREATE TABLE S_TRACKER_LOG (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  TRACKER_SEQ NUMBER(8, 0),
  EMPLOYEE_ID VARCHAR2(40),
  LOG VARCHAR2(200),
  COMMENTS VARCHAR2(1000),
  PERFORMANCE_SEQ NUMBER(8, 0),
  ADD_DATE VARCHAR2(30),
  MOD_DATE VARCHAR2(30)
);
DROP SEQUENCE tracker_log_seq;
CREATE SEQUENCE tracker_log_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_tracker_log_seq
BEFORE INSERT    --
ON S_TRACKER_LOG
FOR EACH ROW
BEGIN
  SELECT tracker_log_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_TRACKER_LOG(TRACKER_SEQ, EMPLOYEE_ID, LOG, COMMENTS, PERFORMANCE_SEQ, ADD_DATE, MOD_DATE) VALUES(1, '10002', 'Evaluation for Tech Skills', 'Good', 1, '2014-09-10 11:11:11', '2014-09-11 11:11:11');
INSERT INTO S_TRACKER_LOG(TRACKER_SEQ, EMPLOYEE_ID, LOG, COMMENTS, PERFORMANCE_SEQ, ADD_DATE, MOD_DATE) VALUES(1, '10002', 'Evaluation for Communication Skills', 'Pool', 2, '2014-09-10 11:11:11', '2014-09-11 11:11:11');
INSERT INTO S_TRACKER_LOG(TRACKER_SEQ, EMPLOYEE_ID, LOG, COMMENTS, PERFORMANCE_SEQ, ADD_DATE, MOD_DATE) VALUES(1, '10003', 'Evaluation for Motivation', 'Perfect', 1, '2014-09-10 11:11:11', '2014-09-11 11:11:11');



DROP TABLE S_PERFORMANCE;
CREATE TABLE S_PERFORMANCE (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(40)
);
DROP SEQUENCE performance_seq;
CREATE SEQUENCE performance_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_performance_seq
BEFORE INSERT    --
ON S_PERFORMANCE
FOR EACH ROW
BEGIN
  SELECT performance_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_PERFORMANCE(NAME) VALUES('Positive');
INSERT INTO S_PERFORMANCE(NAME) VALUES('Negtive');

DROP TABLE S_TASK;
CREATE TABLE S_TASK (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(40),
  DESCRIPTION VARCHAR2(1000)
);
DROP SEQUENCE task_seq;
CREATE SEQUENCE task_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_task_seq
BEFORE INSERT    --
ON S_TASK
FOR EACH ROW
BEGIN
  SELECT task_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_TASK(NAME, DESCRIPTION) VALUES('Make Issue 1 For Times', 'Make the first issue for newspaper Times');
INSERT INTO S_TASK(NAME, DESCRIPTION) VALUES('Buy Papers', 'Buy papers to make newspapers');


DROP TABLE S_TASK_ADMIN;
CREATE TABLE S_TASK_ADMIN (
  TASK_SEQ NUMBER(8, 0),
  EMPLOYEE_ID VARCHAR2(30)
);
INSERT INTO S_TASK_ADMIN(TASK_SEQ, EMPLOYEE_ID) VALUES(1, 10001);
INSERT INTO S_TASK_ADMIN(TASK_SEQ, EMPLOYEE_ID) VALUES(1, 10002);
INSERT INTO S_TASK_ADMIN(TASK_SEQ, EMPLOYEE_ID) VALUES(1, 10003);

INSERT INTO S_TASK_ADMIN(TASK_SEQ, EMPLOYEE_ID) VALUES(2, 10004);
INSERT INTO S_TASK_ADMIN(TASK_SEQ, EMPLOYEE_ID) VALUES(2, 10005);
INSERT INTO S_TASK_ADMIN(TASK_SEQ, EMPLOYEE_ID) VALUES(2, 10006);


DROP TABLE S_ACTIVITY;
CREATE TABLE S_ACTIVITY (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  TASK_SEQ NUMBER(8, 0),
  NAME VARCHAR2(40)
);
DROP SEQUENCE activity_seq;
CREATE SEQUENCE activity_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_activity_seq
BEFORE INSERT    --
ON S_ACTIVITY
FOR EACH ROW
BEGIN
  SELECT activity_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_ACTIVITY(TASK_SEQ, NAME) VALUES(1, 'Find News');
INSERT INTO S_ACTIVITY(TASK_SEQ, NAME) VALUES(1, 'Write Articles');
INSERT INTO S_ACTIVITY(TASK_SEQ, NAME) VALUES(1, 'Make Interview');
INSERT INTO S_ACTIVITY(TASK_SEQ, NAME) VALUES(1, 'Arrange Page Layout');

DROP TABLE S_GOAL;
CREATE TABLE S_GOAL (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(100),
  COMPLETION NUMBER(3),
  EMPLOYEE_ID VARCHAR2(40),
  DUE VARCHAR2(30),
  STATUS_SEQ NUMBER(8, 0),
  TYPE_SEQ NUMBER(8, 0),
  DESCRIPTION VARCHAR2(500)
);
DROP SEQUENCE goal_seq;
CREATE SEQUENCE goal_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_goal_seq
BEFORE INSERT    --
ON S_GOAL
FOR EACH ROW
BEGIN
  SELECT goal_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_GOAL(NAME, COMPLETION, EMPLOYEE_ID, DUE, STATUS_SEQ, TYPE_SEQ) VALUES('Carry out HR functions efficiently', 30, 10001, '2014-10-10', 1, 1);
INSERT INTO S_GOAL(NAME, COMPLETION, EMPLOYEE_ID, DUE, STATUS_SEQ, TYPE_SEQ) VALUES('Increase company revenue', 0, 10002, '2014-10-10', 1, 1);
INSERT INTO S_GOAL(NAME, COMPLETION, EMPLOYEE_ID, DUE, STATUS_SEQ, TYPE_SEQ) VALUES('Finish Issue 5 of Fortune', 15, 10003, '2014-10-10', 1, 1);
INSERT INTO S_GOAL(NAME, COMPLETION, EMPLOYEE_ID, DUE, STATUS_SEQ, TYPE_SEQ) VALUES('Detect and notify re-host violations', 40, 10004, '2014-10-10', 1, 2);
INSERT INTO S_GOAL(NAME, COMPLETION, EMPLOYEE_ID, DUE, STATUS_SEQ, TYPE_SEQ) VALUES('Perform ICT related tasks smoothly', 50, 10005, '2014-10-10', 1, 2);
INSERT INTO S_GOAL(NAME, COMPLETION, EMPLOYEE_ID, DUE, STATUS_SEQ, TYPE_SEQ) VALUES('Support financial operations and strategic initiatives', 80, 10006, '2014-10-10', 1, 2);


DROP TABLE S_TIMESHEET;
CREATE TABLE S_TIMESHEET (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  EMPLOYEE_ID VARCHAR2(30),
  START_DATE VARCHAR2(30),
  END_DATE VARCHAR2(30)
);
DROP SEQUENCE timesheet_seq;
CREATE SEQUENCE timesheet_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_timesheet_seq
BEFORE INSERT    --
ON S_TIMESHEET
FOR EACH ROW
BEGIN
  SELECT timesheet_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10001, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10002, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10003, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10004, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10005, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10006, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10007, '2014-09-29', '2014-10-05');
INSERT INTO S_TIMESHEET(EMPLOYEE_ID, START_DATE, END_DATE) VALUES(10008, '2014-09-29', '2014-10-05');


DROP TABLE S_TIMESHEET_PER_WEEK;
CREATE TABLE S_TIMESHEET_PER_WEEK (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  TIMESHEET_SEQ NUMBER(8, 0),
  ACTIVITY_SEQ NUMBER(8, 0),
  TIME1 VARCHAR2(30),
  TIME2 VARCHAR2(30),
  TIME3 VARCHAR2(30),
  TIME4 VARCHAR2(30),
  TIME5 VARCHAR2(30),
  TIME6 VARCHAR2(30),
  TIME7 VARCHAR2(30)
);
DROP SEQUENCE per_week_seq;
CREATE SEQUENCE per_week_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_per_week_seq
BEFORE INSERT    --
ON S_TIMESHEET_PER_WEEK
FOR EACH ROW
BEGIN
  SELECT per_week_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

INSERT INTO S_TIMESHEET_PER_WEEK(TIMESHEET_SEQ, ACTIVITY_SEQ, TIME1, TIME2, TIME3, TIME4, TIME5, TIME6, TIME7) VALUES(1, 1, '0000-00-00 02:00:00', '0000-00-00 03:00:00', '0000-00-00 01:00:00', '0000-00-00 04:00:00', '0000-00-00 01:00:00', '0000-00-00 00:00:00','0000-00-00 00:00:00');
INSERT INTO S_TIMESHEET_PER_WEEK(TIMESHEET_SEQ, ACTIVITY_SEQ, TIME1, TIME2, TIME3, TIME4, TIME5, TIME6, TIME7) VALUES(1, 1, '0000-00-00 01:00:00', '0000-00-00 02:00:00', '0000-00-00 03:00:00', '0000-00-00 04:00:00', '0000-00-00 05:00:00', '0000-00-00 00:00:00','0000-00-00 00:00:00');
INSERT INTO S_TIMESHEET_PER_WEEK(TIMESHEET_SEQ, ACTIVITY_SEQ, TIME1, TIME2, TIME3, TIME4, TIME5, TIME6, TIME7) VALUES(1, 2, '0000-00-00 04:00:00', '0000-00-00 03:00:00', '0000-00-00 02:00:00', '0000-00-00 01:00:00', '0000-00-00 01:00:00', '0000-00-00 00:00:00','0000-00-00 00:00:00');

DELETE FROM S_EMPLOYEES;
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10001, 'Allen Miche', 'm',  '1988-11-11', '12300001111', 'allen@worksap.co.jp', 1, 1, 1, 1, 10002, '10001.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10002,'Byron Brown', 'F', '1989-12-11', '15566666666','michel@worksap.co.jp', 2, 1, 1, 1, 10003, '10002.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10003,'Watson Andrew', 'M', '1987-10-01', '15522222222','caption@worksap.co.jp', 3, 2, 1,1,  10004, '10003.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10004,'Lei Li', 'M', '1987-10-01', '15522222222','Lei@worksap.co.jp', 3, 2, 1,1,  10001, '10004.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10005,'Oska Higo', 'M', '1987-10-01', '15522222222','Oska@worksap.co.jp', 3, 1, 2,1,  10002, '10005.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10006,'Mei Han', 'M', '1987-10-01', '15522222222','Mei@worksap.co.jp', 3, 1, 2,1,  10001, '10006.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10007,'John Mike', 'M', '1987-10-01', '15522222222','John@worksap.co.jp', 3, 2, 2,1,  10005, '10007.jpg');
INSERT INTO S_EMPLOYEES ( ID, NAME, SEX, BIRTH, PHONE, EMAIL, JOBTITLE_SEQ, STATUS_SEQ, JOBCATEGORY_SEQ, DEPARTMENT_SEQ, SUPERVISOR_ID, PHOTONAME)
	VALUES (10008,'Lisa Kucho', 'F', '1987-10-21', '15522222222','Kucho@worksap.co.jp', 4, 2, 3,3,  10005, '10008.jpg');

INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10001, '2014-11-01 09:00:00', 'come to office', '2014-11-01 18:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10001, '2014-11-02 10:00:00', 'come to office', '2014-11-02 18:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10001, '2014-11-03 09:20:00', 'come to office', '2014-11-03 17:45:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10001, '2014-11-04 10:10:00', 'come to office', '2014-11-04 20:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10001, '2014-11-05 08:50:00', 'come to office', '2014-11-05 19:30:00', 'go home');

INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10002, '2014-11-01 14:00:00', 'come to office', '2014-11-01 18:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10002, '2014-11-02 10:00:00', 'come to office', '2014-11-02 12:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10002, '2014-11-03 07:20:00', 'come to office', '2014-11-03 21:45:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10002, '2014-11-04 10:10:00', 'come to office', '2014-11-04 23:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10002, '2014-11-05 08:50:00', 'come to office', '2014-11-05 14:30:00', 'go home');

INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10003, '2014-11-01 04:00:00', 'come to office', '2014-11-01 21:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10003, '2014-11-02 09:00:00', 'come to office', '2014-11-02 15:30:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10003, '2014-11-03 07:53:00', 'come to office', '2014-11-03 17:45:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10003, '2014-11-04 10:27:00', 'come to office', '2014-11-04 22:22:00', 'go home');
INSERT INTO S_PUNCH(EMPLOYEEID, INTIME, INNOTE, OUTTIME, OUTNOTE) VALUES (10003, '2014-11-05 05:50:00', 'come to office', '2014-11-05 14:30:00', 'go home');

INSERT INTO S_ROLE ( ROLE ) VALUES ('admin');
INSERT INTO S_ROLE ( ROLE ) VALUES ('employee');
INSERT INTO S_ROLE ( ROLE ) VALUES ('editor');
INSERT INTO S_ROLE ( ROLE ) VALUES ('HR');
INSERT INTO S_ROLE ( ROLE ) VALUES ('salesman');

INSERT INTO S_USERS ( PASSWORD, USERNAME, ROLEID, EMPLOYEEID)
	VALUES ('5f4dcc3b5aa765d61d8327deb882cf99','admin', 1,10001);
INSERT INTO S_USERS ( PASSWORD, USERNAME, ROLEID, EMPLOYEEID)
	VALUES ('5f4dcc3b5aa765d61d8327deb882cf99','user', 2,10001);

INSERT INTO S_DEPARTMENT (NAME,PARENT_SEQ) VALUES('Worksap',  0);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('HR Department', 1);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('IT Dept',  1);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('Engineering',  1);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('Accounts Dept',  1);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('Industry',  4);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('Analysis',  3);
INSERT INTO S_DEPARTMENT (NAME, PARENT_SEQ) VALUES('Sales Division', 1);

INSERT INTO S_STATUS (NAME) VALUES( 'Full time Contract');
INSERT INTO S_STATUS (NAME) VALUES( 'Full time Permanent');
INSERT INTO S_STATUS (NAME) VALUES( 'Full time Probation');
INSERT INTO S_STATUS (NAME) VALUES( 'Part time Contract');
INSERT INTO S_STATUS (NAME) VALUES( 'Part time Internship');
INSERT INTO S_STATUS (NAME) VALUES( 'Part time Permanent');

INSERT INTO S_GOAL_STATUS (NAME) VALUES( 'Achieved');
INSERT INTO S_GOAL_STATUS (NAME) VALUES( 'In Progress');
INSERT INTO S_GOAL_STATUS (NAME) VALUES( 'Pending');

INSERT INTO S_GOAL_TYPE (NAME) VALUES( 'Individual');
INSERT INTO S_GOAL_TYPE (NAME) VALUES( 'Business');

INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Craft Workers');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Laborers and Helpers');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Office and Clerical Workers');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Officials and Managers');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Operatives');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Professionals');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Sales Workers');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Service Workers');
INSERT INTO S_JOBCATEGORY (NAME) VALUES( 'Technicians');

INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Accountant');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Audit Trainee');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Community Planner');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Economist');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Head of Finance');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'HR Specialist');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Industrial Engineer');
INSERT INTO S_JOBTITLE ( NAME) VALUES( 'Program Manager');

INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Train tools and manage services', 'Trains others on tools and techniques for monitoring and managing service or system capacities.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Strong Desire for Success', 'He/she does have the burning desire to achieve success in sales.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Recovering from Rejection', 'when the salesperson gets rejected it will not take hours, days or weeks before he/she feels sufficiently confident to effectively make another cold call.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Positive Commitment', 'They will accomplish any sales activity what they set out to.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Planning Methodologies', 'Discusses alternative capability planning methodologies specific to corresponding technology domains.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Personally credible.', 'Expert in both HR and commercial issues, and takes a professional approach.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Oversees computer operations', 'Oversees computer operations and ensures that schedules are met and system resources are used effectively.');
INSERT INTO S_COMPETENCY ( NAME, DESCRIPTION) VALUES( 'Monitor system progress', 'Compares actual and optimal measurements and monitors them on an ongoing basis.');

INSERT INTO S_POSITION ( NAME) VALUES( 'In Service');
INSERT INTO S_POSITION ( NAME) VALUES( 'Dismission');
INSERT INTO S_POSITION ( NAME) VALUES( 'Retired');

INSERT INTO S_SEX (NAME) VALUES('M');
INSERT INTO S_SEX (NAME) VALUES('F');

DROP TABLE N_SUBJECT;
CREATE TABLE N_SUBJECT
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(100)
);
DROP SEQUENCE subject_seq;
CREATE SEQUENCE subject_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_subject_seq
BEFORE INSERT    --
ON N_SUBJECT
FOR EACH ROW
BEGIN
  SELECT subject_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/



DROP TABLE N_ARTICLE;
CREATE TABLE N_ARTICLE
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	TITLE VARCHAR2(200),
	HTML CLOB,
	SECTION_SEQ NUMBER(8, 0),
	SUBJECT_SEQ NUMBER(8,0),
	AUTHOR_SEQ NUMBER(8, 0),
	TIME VARCHAR2(30),
	STATUS_SEQ NUMBER(8, 0)
);
DROP SEQUENCE article_seq;
CREATE SEQUENCE article_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_article_seq
BEFORE INSERT    --
ON N_ARTICLE
FOR EACH ROW
BEGIN
  SELECT article_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_AUTHOR;
CREATE TABLE N_AUTHOR
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(50),
	ADDRESS VARCHAR2(200),
	WORKUNIT VARCHAR2(200),
	ZIPCODE VARCHAR2(30),
	PHONE VARCHAR2(20),
	EMAIL VARCHAR2(50),
	ACCOUNT VARCHAR2(50)
);
DROP SEQUENCE author_seq;
CREATE SEQUENCE author_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_author_seq
BEFORE INSERT    --
ON N_AUTHOR
FOR EACH ROW
BEGIN
  SELECT author_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_NEWSPAPER;
CREATE TABLE N_NEWSPAPER
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(100),
	ISSN VARCHAR2(100),
	ISSUE_PRICE NUMBER(8, 2),
	MONTH_PRICE NUMBER(8, 2),
	PDAY NUMBER(8, 0),
	PTYPE NUMBER(8, 0)
);
DROP SEQUENCE newspaper_seq;
CREATE SEQUENCE newspaper_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_newspaper_seq
BEFORE INSERT    --
ON N_NEWSPAPER
FOR EACH ROW
BEGIN
  SELECT newspaper_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_ISSUE;
CREATE TABLE N_ISSUE
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NUM NUMBER(8, 0),
	QUANTITY NUMBER(8, 0),
	TIME VARCHAR2(30),
	NEWSPAPER_SEQ NUMBER(8, 0),
	STATUS_SEQ NUMBER(8, 0)
);
DROP SEQUENCE issue_seq;
CREATE SEQUENCE issue_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_issue_seq
BEFORE INSERT    --
ON N_ISSUE
FOR EACH ROW
BEGIN
  SELECT issue_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_SECTION;
CREATE TABLE N_SECTION
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NUM NUMBER(8, 0),
	SUBJECT_SEQ NUMBER(8, 0),
	ISSUE_SEQ NUMBER(8, 0),
	STATUS_SEQ NUMBER(8, 0),
	PHOTONAME VARCHAR2(200),
	HTML CLOB
);
DROP SEQUENCE section_seq;
CREATE SEQUENCE section_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_section_seq
BEFORE INSERT    --
ON N_SECTION
FOR EACH ROW
BEGIN
  SELECT section_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_STATUS;
CREATE TABLE N_STATUS
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(30)
);
DROP SEQUENCE n_status_seq;
CREATE SEQUENCE n_status_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_status_seq
BEFORE INSERT    --
ON N_STATUS
FOR EACH ROW
BEGIN
  SELECT n_status_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_PDATE;
CREATE TABLE N_PDATE
(
	PDAY NUMBER(8, 0),
	PTYPE NUMBER(8, 0)
);

DROP TABLE N_PAYTYPE;
CREATE TABLE N_PAYTYPE
(
	SEQ NUMBER(8, 0),
	NAME VARCHAR2(30)
);
DROP SEQUENCE n_paytype_seq;
CREATE SEQUENCE n_paytype_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_paytype_seq
BEFORE INSERT    --
ON N_PAYTYPE
FOR EACH ROW
BEGIN
  SELECT n_paytype_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/



DROP TABLE N_SUBSCRIBER;
CREATE TABLE N_SUBSCRIBER
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(30),
	ADDRESS VARCHAR2(100),
	ZIPCODE VARCHAR2(30),
	NEWSPAPER_SEQ NUMBER(8, 0),
	DATE_FROM VARCHAR2(30),
	DATE_TO VARCHAR2(30),
	ORDER_DATE VARCHAR2(30),
	QUANTITY NUMBER(8, 0),
	DISCOUNT NUMBER(8, 2),
	MONEY_PAY NUMBER(10, 2),
	EMPLOYEE_ID VARCHAR2(20),
	PAYTYPE_SEQ NUMBER(8, 0),
	PROMOTION_SEQ NUMBER(8, 0),
	AGE NUMBER(5, 0),
	SEX VARCHAR2(30),
	OCCUPATION_SEQ NUMBER(8, 0),
	EDUCATION_SEQ NUMBER(8, 0)

);
DROP SEQUENCE n_subscriber_seq;
CREATE SEQUENCE n_subscriber_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_subscriber_seq
BEFORE INSERT    --
ON N_SUBSCRIBER
FOR EACH ROW
BEGIN
  SELECT n_subscriber_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/


DROP TABLE N_PURPOSE;
CREATE TABLE N_PURPOSE
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(30)
);
DROP SEQUENCE n_purpose_seq;
CREATE SEQUENCE n_purpose_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_purpose_seq
BEFORE INSERT    --
ON N_PURPOSE
FOR EACH ROW
BEGIN
  SELECT n_purpose_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_NONSUBSCRIBER;
CREATE TABLE N_NONSUBSCRIBER
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(30),
	ADDRESS VARCHAR2(100),
	ZIPCODE VARCHAR2(30),
	WORKUNIT VARCHAR2(100),
	ISSUE_SEQ NUMBER(8, 0),
	QUANTITY NUMBER(8, 0),
	PURPOSE_SEQ NUMBER(8, 0),
	PHONE VARCHAR2(30),
	EMAIL VARCHAR2(30),
	EMPLOYEE_ID VARCHAR2(20),
	ORDER_DATE VARCHAR2(30),
	PROMOTION_SEQ NUMBER(8, 0),
	AGE NUMBER(5, 0),
	SEX VARCHAR2(30),
	OCCUPATION_SEQ NUMBER(8, 0),
	EDUCATION_SEQ NUMBER(8, 0)
);
DROP SEQUENCE n_nonsubscriber_seq;
CREATE SEQUENCE n_nonsubscriber_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_nonsubscriber_seq
BEFORE INSERT    --
ON N_NONSUBSCRIBER
FOR EACH ROW
BEGIN
  SELECT n_nonsubscriber_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/


DROP TABLE N_RETAIL;
CREATE TABLE N_RETAIL
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	ISSUE_SEQ NUMBER(8, 0),
	UNITPRICE NUMBER(10, 2),
	QUANTITY NUMBER(8, 0),
	DISCOUNT NUMBER(8, 2),
	MONEYPAY NUMBER(10, 2),
	EMPLOYEE_ID VARCHAR2(20),
	ORDER_DATE VARCHAR2(30),
	PROMOTION_SEQ NUMBER(8, 0),
	AGE NUMBER(5, 0),
	SEX VARCHAR2(30),
	OCCUPATION_SEQ NUMBER(8, 0),
	EDUCATION_SEQ NUMBER(8, 0)
);
DROP SEQUENCE n_retail_seq;
CREATE SEQUENCE n_retail_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_retail_seq
BEFORE INSERT    --
ON N_RETAIL
FOR EACH ROW
BEGIN
  SELECT n_retail_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/


DROP TABLE N_RETURN;
CREATE TABLE N_RETURN
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	ISSUE_SEQ NUMBER(8, 0),
	UNITPRICE NUMBER(10, 2),
	QUANTITY NUMBER(8, 0),
	DISCOUNT NUMBER(8, 2),
	EMPLOYEE_ID VARCHAR2(20),
	ORDER_DATE VARCHAR2(30),
	PROMOTION_SEQ NUMBER(8, 0)
);
DROP SEQUENCE n_return_seq;
CREATE SEQUENCE n_return_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_return_seq
BEFORE INSERT    --
ON N_RETURN
FOR EACH ROW
BEGIN
  SELECT n_return_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

DROP TABLE N_PROMOTION;
CREATE TABLE N_PROMOTION
(
	SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
	NAME VARCHAR2(100),
	PLACE VARCHAR2(300),
	START_DATE VARCHAR2(30),
	END_DATE VARCHAR2(30)
);
DROP SEQUENCE n_promotion_seq;
CREATE SEQUENCE n_promotion_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_n_promotion_seq
BEFORE INSERT    --
ON N_PROMOTION
FOR EACH ROW
BEGIN
  SELECT n_promotion_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

INSERT INTO N_PROMOTION(NAME, PLACE, START_DATE, END_DATE) VALUES('Regular Customer', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
INSERT INTO N_PROMOTION(NAME, PLACE, START_DATE, END_DATE) VALUES('Post Office', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
INSERT INTO N_PROMOTION(NAME, PLACE, START_DATE, END_DATE) VALUES('Ad in JiaoTong University', 'JiaoTong University', '2014-09-01 00:00:00', '2014-12-01 00:00:00');
INSERT INTO N_PROMOTION(NAME, PLACE, START_DATE, END_DATE) VALUES('Promotion in Summit Center', 'Summit Center', '2014-09-01 00:00:00', '2014-12-01 00:00:00');


DROP TABLE N_PROMOTION_ADMIN;
CREATE TABLE N_PROMOTION_ADMIN
(
	PROMOTION_SEQ NUMBER(8, 0),
	EMPLOYEE_ID VARCHAR2(30)
);

DROP TABLE N_PROMOTION_NEWSPAPER;
CREATE TABLE N_PROMOTION_NEWSPAPER
(
	PROMOTION_SEQ NUMBER(8, 0),
	NEWSPAPER_SEQ NUMBER(8, 0)
);


DROP TABLE N_AD;
CREATE TABLE N_AD
(
	ID VARCHAR2(20) NOT NULL PRIMARY KEY,
	WORK_UNIT VARCHAR2(100),
	ADDRESS VARCHAR2(100),
	NEWSPAPER_SEQ NUMBER(8, 0),
	PAGE_COUNT NUMBER(8, 0),
	PAGE_AREA NUMBER(8, 0),
	DATE_FROM VARCHAR2(30),
	DATE_TO VARCHAR2(30),
	UNIT_PRICE NUMBER(10, 2),
	DISCOUNT NUMBER(8, 2),
	MONEY_PAY NUMBER(10, 2),
	CONTACT_PERSON VARCHAR2(20),
	PHONE VARCHAR2(20),
	EMAIL VARCHAR2(20),
	EMPLOYEE_ID VARCHAR2(20),
	ORDER_DATE VARCHAR2(30),
	PROMOTION_SEQ NUMBER(8, 0)
);

DROP TABLE N_OCCUPATION;
CREATE TABLE N_OCCUPATION (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE occupation_seq;
CREATE SEQUENCE occupation_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_occupation_seq
BEFORE INSERT    --
ON N_OCCUPATION
FOR EACH ROW
BEGIN
  SELECT occupation_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/
INSERT INTO N_OCCUPATION(NAME) VALUES ('Student');
INSERT INTO N_OCCUPATION(NAME) VALUES ('Doctor');
INSERT INTO N_OCCUPATION(NAME) VALUES ('Engineer');


DROP TABLE N_EDUCATION;
CREATE TABLE N_EDUCATION (
  SEQ NUMBER(8, 0) NOT NULL PRIMARY KEY,
  NAME VARCHAR2(50)
);
DROP SEQUENCE education_seq;
CREATE SEQUENCE education_seq
INCREMENT BY 1
START WITH 1  --从1开始
NOMAXVALUE    --不设最大值
NOCYCLE       --不循环
NOCACHE;
CREATE TRIGGER increase_education_seq
BEFORE INSERT    --
ON N_EDUCATION
FOR EACH ROW
BEGIN
  SELECT education_seq.Nextval
  INTO:NEW.SEQ  FROM DUAL;
END;
/

INSERT INTO N_EDUCATION(NAME) VALUES ('PHD');
INSERT INTO N_EDUCATION(NAME) VALUES ('Master');
INSERT INTO N_EDUCATION(NAME) VALUES ('Bachelor');
INSERT INTO N_EDUCATION(NAME) VALUES ('High School');
INSERT INTO N_EDUCATION(NAME) VALUES ('Middle School');
INSERT INTO N_EDUCATION(NAME) VALUES ('Primary School');

INSERT INTO N_STATUS(NAME) VALUES ('Approve');
INSERT INTO N_STATUS(NAME) VALUES ('Pending Approval');
INSERT INTO N_STATUS(NAME) VALUES ('Reject');


INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (1, 1); -- type = 1, every month
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (2, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (3, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (4, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (5, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (6, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (7, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (8, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (9, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (10, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (11, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (12, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (13, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (14, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (15, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (16, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (17, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (18, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (19, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (20, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (21, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (22, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (23, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (24, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (25, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (26, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (27, 1);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (28, 1);

INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (1, 2); -- type = 2, every week
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (2, 2);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (3, 2);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (4, 2);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (5, 2);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (6, 2);
INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (7, 2);

INSERT INTO N_PDATE(PDAY, PTYPE) VALUES (1, 3); -- type = 3, every day

INSERT INTO N_NEWSPAPER(NAME, ISSN, ISSUE_PRICE, MONTH_PRICE, PDAY, PTYPE) VALUES ('Fortune', 'ISSN003', 1.0, 8.5, 5, 1);
INSERT INTO N_NEWSPAPER(NAME, ISSN, ISSUE_PRICE, MONTH_PRICE, PDAY, PTYPE) VALUES ('Science', 'ISSN004', 1.0, 9.5, 10, 1);
INSERT INTO N_NEWSPAPER(NAME, ISSN, ISSUE_PRICE, MONTH_PRICE, PDAY, PTYPE) VALUES ('Times', 'ISSN002', 0.5, 6, 2, 2);
INSERT INTO N_NEWSPAPER(NAME, ISSN, ISSUE_PRICE, MONTH_PRICE, PDAY, PTYPE) VALUES ('よみうりしんぶん', 'ISSN004', 1.3, 10, 3, 2);



INSERT INTO N_ISSUE(NUM, TIME, QUANTITY, NEWSPAPER_SEQ, STATUS_SEQ) VALUES (1, '2014-09-15 00:00:00', 2000, 1, 1);
INSERT INTO N_ISSUE(NUM, TIME, QUANTITY, NEWSPAPER_SEQ, STATUS_SEQ) VALUES (1, '2014-09-17 00:00:00', 500, 2, 1);
INSERT INTO N_ISSUE(NUM, TIME, QUANTITY, NEWSPAPER_SEQ, STATUS_SEQ) VALUES (1, '2014-09-19 00:00:00', 30000, 3, 1);
INSERT INTO N_ISSUE(NUM, TIME, QUANTITY, NEWSPAPER_SEQ, STATUS_SEQ) VALUES (1, '2014-09-21 00:00:00', 4600, 4, 1);
INSERT INTO N_ISSUE(NUM, TIME, QUANTITY, NEWSPAPER_SEQ, STATUS_SEQ) VALUES (1, '2014-10-01 00:00:00', 5000, 1, 1);


INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (1, 1, 1, 1, 'Fortune\1\1.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (2, 2, 1, 1, 'Fortune\1\2.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (3, 3, 1, 1, 'Fortune\1\3.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (4, 4, 1, 1, 'Fortune\1\4.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (5, 5, 1, 1, 'Fortune\1\5.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (6, 6, 1, 1, 'Fortune\1\6.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (7, 7, 1, 1, 'Fortune\1\7.jpg');
INSERT INTO N_SECTION(NUM, SUBJECT_SEQ, ISSUE_SEQ, STATUS_SEQ, PHOTONAME) VALUES (8, 8, 1, 1, 'Fortune\1\8.jpg');


INSERT INTO N_ARTICLE(TITLE, HTML, SECTION_SEQ, SUBJECT_SEQ, AUTHOR_SEQ, TIME, STATUS_SEQ) VALUES('Apollo II', 'Apollo II was lanched yesterday.', 1, 1, 1, '2014-09-15 00:00:00', 1);
INSERT INTO N_ARTICLE(TITLE, HTML, SECTION_SEQ, SUBJECT_SEQ, AUTHOR_SEQ, TIME, STATUS_SEQ) VALUES('Alibaba New Business', 'Alibaba has new business related to mobil phone.', 0, 2, 2, '2014-09-16 00:00:00', 1);
INSERT INTO N_ARTICLE(TITLE, HTML, SECTION_SEQ, SUBJECT_SEQ, AUTHOR_SEQ, TIME, STATUS_SEQ) VALUES('Article about Game', 'Some Contents...', 0, 3, 3, '2014-09-17 00:00:00', 1);
INSERT INTO N_ARTICLE(TITLE, HTML, SECTION_SEQ, SUBJECT_SEQ, AUTHOR_SEQ, TIME, STATUS_SEQ) VALUES('Article about Society', 'Some Contents...', 0, 4, 4, '2014-09-18 00:00:00', 1);

INSERT INTO N_AUTHOR(NAME, ADDRESS, WORKUNIT, ZIPCODE, PHONE, EMAIL, ACCOUNT) VALUES ('Jessa John', '101, Yanan Rd', 'Worksap', '734200', '15500001111', 'Jessa@worksap.com', '6321421312321');
INSERT INTO N_AUTHOR(NAME, ADDRESS, WORKUNIT, ZIPCODE, PHONE, EMAIL, ACCOUNT) VALUES ('Lydia Pinkmen', '111, Shanxi Rd', 'Microsoft', '734201', '15500001111', 'Lydia@worksap.com', '6321421312321');
INSERT INTO N_AUTHOR(NAME, ADDRESS, WORKUNIT, ZIPCODE, PHONE, EMAIL, ACCOUNT) VALUES ('Water White', '121, Panyu Rd', 'Apple', '734202', '15500001111', 'Water@worksap.com', '6321421312321');
INSERT INTO N_AUTHOR(NAME, ADDRESS, WORKUNIT, ZIPCODE, PHONE, EMAIL, ACCOUNT) VALUES ('Hank Shield', '131, Huaihai Rd', 'Google', '734203', '15500001111', 'Hank@worksap.com', '6321421312321');
INSERT INTO N_AUTHOR(NAME, ADDRESS, WORKUNIT, ZIPCODE, PHONE, EMAIL, ACCOUNT) VALUES ('Jim Parson', '141, Changfen Rd', 'Tecent', '734204', '15500001111', 'Jim@worksap.com', '6321421312321');


INSERT INTO N_SUBJECT(NAME) VALUES ('Global');
INSERT INTO N_SUBJECT(NAME) VALUES ('Economy');
INSERT INTO N_SUBJECT(NAME) VALUES ('Society');
INSERT INTO N_SUBJECT(NAME) VALUES ('Military');
INSERT INTO N_SUBJECT(NAME) VALUES ('Technology');
INSERT INTO N_SUBJECT(NAME) VALUES ('Entertainment');
INSERT INTO N_SUBJECT(NAME) VALUES ('Game');
INSERT INTO N_SUBJECT(NAME) VALUES ('Fiction');

INSERT INTO N_PAYTYPE(NAME) VALUES('Cash');
INSERT INTO N_PAYTYPE(NAME) VALUES('Pay by mail');
INSERT INTO N_PAYTYPE(NAME) VALUES('Bank transfer');

INSERT INTO N_PURPOSE(NAME) VALUES('Exchange');
INSERT INTO N_PURPOSE(NAME) VALUES('Given Free');

INSERT INTO N_SUBSCRIBER(NAME, ADDRESS, ZIPCODE, NEWSPAPER_SEQ, DATE_FROM, DATE_TO, ORDER_DATE, QUANTITY, DISCOUNT, MONEY_PAY, EMPLOYEE_ID, PAYTYPE_SEQ, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Leared', '101, Yanan Rd', '200052', 1, '2014-09-01 00:00:00', '2015-09-01 00:00:00', '2014-08-10 00:00:00', 100, 0.8, 1000, '10001', 1, 1, 0, 'Not Recorded', 1, 1);
INSERT INTO N_SUBSCRIBER(NAME, ADDRESS, ZIPCODE, NEWSPAPER_SEQ, DATE_FROM, DATE_TO, ORDER_DATE, QUANTITY, DISCOUNT, MONEY_PAY, EMPLOYEE_ID, PAYTYPE_SEQ, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Jack', '101, Yanan Rd', '200052', 2, '2014-09-01 00:00:00', '2015-09-01 00:00:00', '2014-08-10 00:00:00', 200, 0.8, 2000, '10002', 2, 1, 5, 'M', 2, 2);
INSERT INTO N_SUBSCRIBER(NAME, ADDRESS, ZIPCODE, NEWSPAPER_SEQ, DATE_FROM, DATE_TO, ORDER_DATE, QUANTITY, DISCOUNT, MONEY_PAY, EMPLOYEE_ID, PAYTYPE_SEQ, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Caption', '101, Yanan Rd', '200052', 3, '2014-09-01 00:00:00', '2015-09-01 00:00:00', '2014-08-10 00:00:00', 150, 0.7, 3000, '10003', 1, 1, 15, 'Not Recorded', 3, 3);
INSERT INTO N_SUBSCRIBER(NAME, ADDRESS, ZIPCODE, NEWSPAPER_SEQ, DATE_FROM, DATE_TO, ORDER_DATE, QUANTITY, DISCOUNT, MONEY_PAY, EMPLOYEE_ID, PAYTYPE_SEQ, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Maria', '101, Yanan Rd', '200052', 4, '2014-09-01 00:00:00', '2015-09-01 00:00:00', '2014-08-10 00:00:00', 400, 0.9, 4000, '10004', 2, 1, 25, 'F', 1, 4);
INSERT INTO N_SUBSCRIBER(NAME, ADDRESS, ZIPCODE, NEWSPAPER_SEQ, DATE_FROM, DATE_TO, ORDER_DATE, QUANTITY, DISCOUNT, MONEY_PAY, EMPLOYEE_ID, PAYTYPE_SEQ, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Skyler', '101, Yanan Rd', '200052', 1, '2014-09-01 00:00:00', '2015-09-01 00:00:00', '2014-08-10 00:00:00', 100, 0.8, 5000, '10005', 1, 1, 35, 'Not Recorded', 2, 5);

INSERT INTO N_NONSUBSCRIBER(NAME, ADDRESS, ZIPCODE, WORKUNIT, ISSUE_SEQ, QUANTITY, PURPOSE_SEQ, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Leared', '101, Yanan Rd', '200052', 'Alibaba', 1, 100, 1, '15500000000', 'Leared@qq.com', 10001, '2014-09-15 14:00:00', 1, 5, 'Not Recorded', 1, 1);
INSERT INTO N_NONSUBSCRIBER(NAME, ADDRESS, ZIPCODE, WORKUNIT, ISSUE_SEQ, QUANTITY, PURPOSE_SEQ, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Jack', '101, Yanan Rd', '200052', 'Alibaba', 2, 200, 2, '15500000000', 'Jack@qq.com', 10002, '2014-09-15 15:00:00', 2, 5, 'Not Recorded', 2, 2);
INSERT INTO N_NONSUBSCRIBER(NAME, ADDRESS, ZIPCODE, WORKUNIT, ISSUE_SEQ, QUANTITY, PURPOSE_SEQ, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Maria', '101, Yanan Rd', '200052', 'Alibaba', 3, 300, 1, '15500000000', 'Maria@qq.com', 10003, '2014-09-15 16:00:00', 1, 5, 'Not Recorded', 3, 3);
INSERT INTO N_NONSUBSCRIBER(NAME, ADDRESS, ZIPCODE, WORKUNIT, ISSUE_SEQ, QUANTITY, PURPOSE_SEQ, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Skyler', '101, Yanan Rd', '200052', 'Alibaba', 4, 150, 2, '15500000000', 'Skyler@qq.com', 10004, '2014-09-15 17:00:00', 2, 5, 'Not Recorded', 1, 4);
INSERT INTO N_NONSUBSCRIBER(NAME, ADDRESS, ZIPCODE, WORKUNIT, ISSUE_SEQ, QUANTITY, PURPOSE_SEQ, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES('Todd', '101, Yanan Rd', '200052', 'Alibaba', 5, 100, 1, '15500000000', 'Todd@qq.com', 10005, '2014-09-15 18:00:00', 3, 5, 'Not Recorded', 2, 5);

INSERT INTO N_RETAIL(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, MONEYPAY, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES(1, 0.5, 100, 0.8, 300, 10001, '2014-09-15 14:00:00', 1, 5, 'Not Recorded', 0, 1);
INSERT INTO N_RETAIL(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, MONEYPAY, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES(2, 0.6, 200, 0.8, 400, 10002, '2014-09-15 14:00:00', 2, 15, 'Not Recorded', 1, 0);
INSERT INTO N_RETAIL(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, MONEYPAY, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES(3, 0.8, 300, 0.8, 500, 10003, '2014-09-15 14:00:00', 3, 25, 'Not Recorded', 2, 1);
INSERT INTO N_RETAIL(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, MONEYPAY, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES(4, 1.2, 400, 0.8, 600, 10004, '2014-09-15 14:00:00', 1, 1, 'Not Recorded', 0, 2);
INSERT INTO N_RETAIL(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, MONEYPAY, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ, AGE, SEX, OCCUPATION_SEQ, EDUCATION_SEQ) VALUES(5, 1,  500, 0.8, 700, 10005, '2014-09-15 14:00:00', 2, 0, 'Not Recorded', 1, 3);

INSERT INTO N_RETURN(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES(1, 0.1, 1, 0.5, 10002, '2014-09-15 00:00:00', 1);
INSERT INTO N_RETURN(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES(2, 0.1, 2, 0.5, 10002, '2014-09-15 00:00:00', 1);
INSERT INTO N_RETURN(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES(3, 0.1, 3, 0.5, 10002, '2014-09-15 00:00:00', 1);
INSERT INTO N_RETURN(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES(4, 0.1, 4, 0.5, 10002, '2014-09-15 00:00:00', 1);
INSERT INTO N_RETURN(ISSUE_SEQ, UNITPRICE, QUANTITY, DISCOUNT, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES(5, 0.1, 5, 0.5, 10002, '2014-09-15 00:00:00', 1);

INSERT INTO N_AD(ID, WORK_UNIT, ADDRESS, NEWSPAPER_SEQ, PAGE_COUNT, PAGE_AREA, DATE_FROM, DATE_TO, UNIT_PRICE, DISCOUNT, MONEY_PAY, CONTACT_PERSON, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES('AD1', 'WU1', 'ADD1', 1, 1, 1, '2014-09-15 00:00:00', '2014-10-15 00:00:00', 1, 0.5, 100, 'CP1', '155', 'XX@XX.XX', '10001', '2014-02-02 00:00:00', 1);
INSERT INTO N_AD(ID, WORK_UNIT, ADDRESS, NEWSPAPER_SEQ, PAGE_COUNT, PAGE_AREA, DATE_FROM, DATE_TO, UNIT_PRICE, DISCOUNT, MONEY_PAY, CONTACT_PERSON, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES('AD2', 'WU1', 'ADD1', 2, 1, 1, '2014-09-15 00:00:00', '2014-10-15 00:00:00', 2, 0.5, 200, 'CP1', '155', 'XX@XX.XX', '10001', '2014-02-02 00:00:00', 1);
INSERT INTO N_AD(ID, WORK_UNIT, ADDRESS, NEWSPAPER_SEQ, PAGE_COUNT, PAGE_AREA, DATE_FROM, DATE_TO, UNIT_PRICE, DISCOUNT, MONEY_PAY, CONTACT_PERSON, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES('AD3', 'WU1', 'ADD1', 3, 1, 1, '2014-09-15 00:00:00', '2014-10-15 00:00:00', 3, 0.5, 300, 'CP1', '155', 'XX@XX.XX', '10001', '2014-02-02 00:00:00', 1);
INSERT INTO N_AD(ID, WORK_UNIT, ADDRESS, NEWSPAPER_SEQ, PAGE_COUNT, PAGE_AREA, DATE_FROM, DATE_TO, UNIT_PRICE, DISCOUNT, MONEY_PAY, CONTACT_PERSON, PHONE, EMAIL, EMPLOYEE_ID, ORDER_DATE, PROMOTION_SEQ) VALUES('AD4', 'WU1', 'ADD1', 4, 1, 1, '2014-09-15 00:00:00', '2014-10-15 00:00:00', 4, 0.5, 400, 'CP1', '155', 'XX@XX.XX', '10001', '2014-02-02 00:00:00', 1);

INSERT INTO N_PROMOTION_ADMIN(PROMOTION_SEQ, EMPLOYEE_ID) VALUES(3, 10001);
INSERT INTO N_PROMOTION_ADMIN(PROMOTION_SEQ, EMPLOYEE_ID) VALUES(3, 10002);
INSERT INTO N_PROMOTION_ADMIN(PROMOTION_SEQ, EMPLOYEE_ID) VALUES(3, 10003);


INSERT INTO N_PROMOTION_NEWSPAPER(PROMOTION_SEQ, NEWSPAPER_SEQ) VALUES (3, 1);
INSERT INTO N_PROMOTION_NEWSPAPER(PROMOTION_SEQ, NEWSPAPER_SEQ) VALUES (3, 2);
INSERT INTO N_PROMOTION_NEWSPAPER(PROMOTION_SEQ, NEWSPAPER_SEQ) VALUES (3, 3);

INSERT INTO N_PROMOTION_ADMIN(PROMOTION_SEQ, EMPLOYEE_ID) VALUES(4, 10004);
INSERT INTO N_PROMOTION_ADMIN(PROMOTION_SEQ, EMPLOYEE_ID) VALUES(4, 10005);
INSERT INTO N_PROMOTION_ADMIN(PROMOTION_SEQ, EMPLOYEE_ID) VALUES(4, 10006);

INSERT INTO N_PROMOTION_NEWSPAPER(PROMOTION_SEQ, NEWSPAPER_SEQ) VALUES (4, 3);
INSERT INTO N_PROMOTION_NEWSPAPER(PROMOTION_SEQ, NEWSPAPER_SEQ) VALUES (4, 4);
